<?php

namespace MLM\Model\Transaction;

use MLM\Model\Transaction;

class Storage extends \MLM\Model\Generic\Db\Storage {



    public function getTransType($transTypeCode = "", $transTypeCodeIn = ""){
        $sql = "select * from trans_type WHERE is_used = 'Y' ";

        if ($transTypeCode != ""){
            $sql .= " and trans_type_code like '%$transTypeCode%' ";
        }

        if ($transTypeCodeIn != ""){
            $sql .= " and trans_type_code in ($transTypeCodeIn) ";
        }

        $sql .= " order by trans_type_name ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        return $result;
    }


    public function getTrackingTransaction($accNum, $transType, $date1, $date2, $start, $limit, $searchField = "", $mutation = "") {

        $sql = "select count(1) as total from (select trans_dtm
                ,sum(amount1) as amount1
                ,trans_type_name,  mutation_type,
                notes_txt,
                account_num, account_name
                from v_transaction
                where STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and account_num1 = '$accNum'  ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        if ($mutation != ""){
            $sql .= " and mutation_type = '$mutation' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }


        $sql .= " group by trans_header_id, trans_dtm ,
                trans_type_name, mutation_type, notes_txt, account_num, account_name) x";

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows, 1);


        $sql = "select
                trans_header_id, trans_code, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm, trans_type_id, trans_type_code,
                trans_type_name, account_num1, account_num2, amount1, amount2, mutation_type,
                notes_txt, trx_type, member_id, trans_status, account_num, account_name,
                external_param, external_resp, xn1, xn2, xs1, xs2, xs3, xs4, xs5, xs6, xn3,
                trans_detail_id, xd1, xd2, bank_acc_no, bank_acc_name, bank_city_name, bank_id
                from v_transaction
                where STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and  account_num1 = '$accNum' ";
        }

        if ($transType != ""){
            $sql .= " and trans_type_code = '$transType' ";
        }

        if ($mutation != ""){
            $sql .= " and mutation_type = '$mutation' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " 
                order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                $kredit = "0";
                $debet = "0";

                if ($res['mutation_type'] == "K"){
                    $kredit = $res['amount1'];
                }else{
                    $debet = $res['amount1'];
                }

                if ($res['xn1'] == 1){  //Bank Wire

                    if ($res['xn3'] == 1) $paymentGW = "Bank Wire";
                    else if ($res['xn3'] == 2) $paymentGW = "Paypall";
                    else if ($res['xn3'] == 3) $paymentGW = "Perfect Money";
                    else $paymentGW = "Ego Pay";

                }else if ($res['xn1'] == 2) $paymentGW = "Perfect Money";
                else if ($res['xn1'] == 3) $paymentGW = "Ego Pay";
                else $paymentGW = "PayPal";

                $form = ($res['xn1'] == 1) ? 1 : 2;

                $res['paymentgw'] = $paymentGW;

                $detail = '<a data-toggle="modal" class="blue" role="button" href="#detail-form'.$form.'" onclick="detailTrans(\''.base64_encode(json_encode($res)).'\')">Details</a>';

                array_push($dataBonus, array("Tanggal" => $res['trans_dtm'],
                                             "JenisTransaksi" => $res['trans_type_name'],
                                             "NoAkun" => $res['account_num'],
                                             "NamaAkun" => $res['account_name'],
                                             "Kredit" => $kredit,
                                             "Debet" => $debet,
                                             "paymentgw" => $paymentGW,
                                             "status" => $res['trans_status'],
                                             "detail" => $detail
                                     ));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function getBuyTransaction($accNum, $transCode, $date1, $date2, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total from (select trans_header_id, trans_code, trans_dtm
                ,sum(amount1) as amount1
                ,FORMAT(sum(amount1),2) as amount_rp
                ,trans_type_name, mutation_type, notes_txt
                from v_transaction
                where (trans_status is null or trans_status = 'APPROVAL') and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";

        if ($accNum != ""){
            $sql .= " and account_num1 = '$accNum' ";
        }

        //if ($transType != ""){
            $sql .= " and trans_type_code in ('T003','T004') ";
        //}

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_code, trans_dtm, trans_type_name, mutation_type, notes_txt) x ";

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
        }

        $data = array($rows);



        $sql = "select trans_header_id, trans_code, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm
                ,sum(amount1) as amount1
                ,FORMAT(sum(amount1),2) as amount_rp
                ,trans_type_name, mutation_type, notes_txt
                from v_transaction
                where (trans_status is null or trans_status = 'APPROVAL') and STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')
                ";

        if ($accNum != ""){
            $sql .= " and account_num1 = '$accNum' ";
        }

        //if ($transType != ""){
            $sql .= " and trans_type_code in ('T003','T004') ";
        //}

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_code, trans_dtm, trans_type_name, mutation_type, notes_txt
                  order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBuy = array();

        while($result->next()){
                $res = $result->current();

                $aksi = "<a href='javascript: void(0)' onclick='payconfirm(".$res['trans_header_id'].")'>Konfirmasi</a>";

                array_push($dataBuy, array("Tanggal" => $res['trans_dtm'], "JenisTransaksi" => $res['trans_type_name'], "KodeTransaksi" => $res['trans_code'], "Total" => $res['amount1'], "Aksi" => $aksi));


        }


        array_push($data, $dataBuy);

        return $data;
    }


    public function getTotalBalance($key) {

        $sql = "select amount, FORMAT(amount,0) as amount2
                from accounts
                where account_num = ? ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($key));

        return $result;
    }

    public function getTransHeader($transId){


        $sql = "select trans_header_id, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm, trans_code, v.account_num, xs3,
                sum(xn2) as xn2, sum(amount1) as amount1, account_num1
                from v_transaction v
                where trans_header_id = ?
                group by trans_header_id, trans_dtm, trans_code, v.account_num, xs3, account_num1";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        return $result;
    }

    public function getTransDetailProduct($transId){

        $sql = "select suplier_name, member_name, v.xs2, ceil(amount1/xn2) as price, xn2, amount1
                from v_transaction v, supliers s, members m
                where s.suplier_id = v.xs1 and m.member_id=v.xn3
                and trans_header_id = ? ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        $dataConfirm = array();

        $rows = 0;
        while($result->next()){
                $res = $result->current();
                array_push($dataConfirm, array("suplier" => $res['suplier_name'],
                                             "agent" => $res['member_name'],
                                             "prodname" => $res['xs2'],
                                             "price" => $res['price'],
                                             "qty" => $res['xn2'],
                                             "total" => $res['amount1']
                                             ));
                $rows++;
        }

        $data = array($rows);

        array_push($data, $dataConfirm);

        return $data;

    }

    public function getTransDetail($transId){

        $sql = "select *
                from
                v_transaction v
                left outer join bank b
                on b.bank_id = v.bank_id
                where trans_header_id = ?";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        return $result;
    }


    public function getPriceTransDetail($transId){


        $sql = "select trans_header_id, trans_code, sum(amount1) as amount1
                from
                v_transaction v
                where
                trans_header_id = ?
                group by trans_header_id, trans_code";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($transId));

        return $result;
    }


    public function getConfirmPayment($memberTypeId, $memberId, $transCode, $date1, $date2, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total from (select payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount
                ,sum(amount1) as amount1
                from v_transaction v, payment_confirm pc where pc.trans_header_id = v.trans_header_id and
                payment_status = 0 ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($memberTypeId == 4){
            $sql .= " and v.xn3 = 100 ";
            //$sql .= " and v.xn3 is null ";
        }else{
            $sql .= " and v.xn3 = $memberId ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount) x ";


        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
        }

        $data = array($rows);


        $sql = "select payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount
                ,sum(amount1) as amount1
                from v_transaction v, payment_confirm pc where pc.trans_header_id = v.trans_header_id and
                payment_status = 0 ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($memberTypeId == 4){
            //$sql .= " and v.xn3 is null ";
            $sql .= " and v.xn3 = 100 ";
        }else{
            $sql .= " and v.xn3 = $memberId ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by payment_confirm_id, trans_dtm, pay_date, trans_type_name, trans_code, transfer_amount
                 order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataConfirm = array();

        while($result->next()){
                $res = $result->current();
                $aksi = "<a href='javascript: void(0)' onclick='payconfirm(".$res['payment_confirm_id'].")'>Verifikasi</a>";

                array_push($dataConfirm, array("Tgltrans" => $res['trans_dtm'],
                                             "Tgltransfer" => $res['pay_date'],
                                             "JenisTransaksi" => $res['trans_type_name'],
                                             "KodeTransaksi" => $res['trans_code'],
                                             "Total" => $res['amount1'],
                                             "Totaltransfer" => $res['transfer_amount'],
                                             "Aksi" => $aksi));

        }

        array_push($data, $dataConfirm);

        return $data;
    }


    public function getConfirmPaymentDetail($confirmId){

        $sql = "select v.*, pc.*, af.bank_acc_no as from_acc_no,
                af.bank_acc_name as from_acc_name,
                at.bank_acc_no as to_acc_no,
                at.bank_acc_name as to_acc_name, bf.bank_name as from_bank,
                bt.bank_name as to_bank, DATE_FORMAT(pc.pay_date, '%d-%m-%Y') as pay_date2
                from
                v_transaction v, payment_confirm pc, accounts af, accounts at, bank bf, bank bt
                where
                pc.trans_header_id = v.trans_header_id and payment_confirm_id = ?
                and af.account_id = pc.from_account_id and at.account_id = to_account_id
                and bf.bank_id = af.bank_id and bt.bank_id = at.bank_id";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($confirmId));

        return $result;

    }

    public function updateConfirmPaymentStatus($data, $where){

        $sql = $this->update()
                    ->table('payment_confirm')
                    ->set($data)
                    ->where($where);

        $result = $this->execute($sql);

        return $result;

    }



    public function topup($memberId, $bankAccNo, $bank, $jmlTopUp, $tglTransfer, $catatan){

        $dataInput = array($memberId, $bankAccNo, $bank, $jmlTopUp, $tglTransfer, $catatan);

        $stmt = $this->_db->query("CALL Trx_Topup( ?, ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;
    }


    public function getTopupList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T001' and (trans_status is null or trans_status = 'APPROVAL') ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v
                where trans_type_code = 'T001' and (trans_status is null or trans_status = 'APPROVAL') ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $tglTransfer = explode(" ", $res['xd1']);
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrx" => $res['trans_dtm'], "Tgltrans" => $tglTransfer[0],
                                            "NoAkun" => $res['account_num1'], "JmlTopup" => $res['amount1'],
                                            "BankAkun" => $res['xs5'], "Catatan" => $res['xs6'], "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveTopup($transId, $flag){

        $dataInput = array($transId, $flag);


        $stmt = $this->_db->query("CALL Trx_TopupFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }

    public function transfer($memberId, $accountNum, $transfer, $toAccount, $notes){

        $dataInput = array($accountNum, $memberId, $transfer, $toAccount, $notes);

        $stmt = $this->_db->query("CALL Trx_Transfer( ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }

    public function transferwallet($memberId, $transType, $transfer, $fromAccount, $toAccount){

        $dataInput = array($fromAccount, $memberId, $transfer, $toAccount, $transType);

        $stmt = $this->_db->query("CALL Trx_Transferwallet( ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }

    public function withdraw($memberId, $accountNum, $tarik, $bank){

        $dataInput = array($accountNum, $memberId, $tarik, $bank);

        $stmt = $this->_db->query("CALL Trx_Withdraw( ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }


    public function withdrawWallet($memberId, $memberCode, $through, $wallet, $amount, $toAccount, $toAccName, $accType, $bankName, $swiftCode, $address, $bankAddr, $info){

        $dataInput = array($memberId, $memberCode, $through, $wallet, $amount, $toAccount, $toAccName, $accType, $bankName, $swiftCode, $address, $bankAddr, $info);

        $stmt = $this->_db->query("CALL Trx_WithdrawWallet( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");
        $data = $stmt->execute();

        return $data;

    }


    public function getWithdrawList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T005' and v.trans_status = 'APPROVAL' ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select 
                trans_header_id, trans_code, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm, trans_type_id, trans_type_code, trans_type_name,
                account_num1, account_num2, amount1, amount2, mutation_type, notes_txt, trx_type,
                member_id, trans_status, account_num, account_name, external_param, external_resp,
                xn1, xn2, xs1, xs2, xs3, xs4, xs5, xs6, xn3, trans_detail_id, xd1, xd2, bank_acc_no,
                bank_acc_name, bank_city_name, v.bank_id, b.*
                from v_transaction v
                left outer join bank b on b.bank_id = v.bank_id
                where trans_type_code = 'T005'
                and v.trans_status = 'APPROVAL' ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataWD = array();
        while($result->next()){
                $res = $result->current();

                $paymentGW = "";
                $bank = "";

                $form = ($res['xn1'] == 1) ? 1 : 2;

                if ($res["trans_type_code"] == "T005"){

                    $bank = $res['xs2'];

                    if ($res['xn1'] == 1){  //Bank Wire
                        $bank = "[".$res['bank_acc_no']."] ".$res['bank_name']." ".$res['bank_acc_name'];

                        if ($res['xn3'] == 1) $paymentGW = "Bank Wire";
                        else if ($res['xn3'] == 2) $paymentGW = "Paypall";
                        else if ($res['xn3'] == 3) $paymentGW = "Perfect Money";
                        else $paymentGW = "Ego Pay";



                    }else if ($res['xn1'] == 2) $paymentGW = "Perfect Money";
                    else if ($res['xn1'] == 3) $paymentGW = "Ego Pay";
                    else if ($res['xn1'] == 4) $paymentGW = "PayPal";

                }

                $res["paymentgw"] = $paymentGW;

                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                $aksiPop = '<a data-toggle="modal" class="blue" role="button" href="#detail-form'.$form.'" onclick="detailTrans(\''.base64_encode(json_encode($res)).'\')">Approval</a>';

                array_push($dataWD, array("Tgltrans" => $res['trans_dtm'],
                                             "NoAkun" => $res['account_num'],
                                             "JmlPenarikan" => $res['amount1'],
                                             "BankAkun" => $bank,
                                             "Through" => $paymentGW,
                                             "Aksi" => $aksi,
                                             "AksiPop" => $aksiPop,
                                         ));
        }


        array_push($data, $dataWD);

        return $data;
    }

    public function approveWithdraw($transId, $flag){

        $dataInput = array($transId, $flag);


        $stmt = $this->_db->query("CALL Trx_WithdrawFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function getBuySNList($transCode, $date1, $date2, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T003'
                and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v, bank b
                where trans_type_code = 'T003'
                and b.bank_id = v.bank_id
                and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";


        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                if ($res['xs3'] == 1) $bayar = "Cash";
                else if ($res['xs3'] == 2) $bayar = "Tukar Point";
                else $bayar = "Transfer";

                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'], "KodeTrans" => $res['trans_code'], "NoAkun" => $res['account_num'], "JmlPembelian" => $res['xn2'], "Total" => $res['amount1'], "Pembayaran" => $bayar, "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveBuySN($transId, $flag){

        $dataInput = array($transId, $flag);


        $stmt = $this->_db->query("CALL Trx_BuySNFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function getBuyProductList($memberId, $transCode, $date1, $date2, $start, $limit, $searchField = "", $memberType = "") {

        $sql = "select count(1) as total
                from (select trans_header_id, trans_dtm, trans_code, v.account_num, xs3,
                sum(xn2) as xn2, sum(amount1) as amount1
                from v_transaction v
                where trans_type_code = 'T004' and v.trans_status in ('APPROVAL', 'PAY_CONFIRM') ";

        if ($memberType == 4){
            $sql .= " and (v.xn3 is null or v.xn3 = 100) ";
        }else $sql .= " and v.xn3 = $memberId  ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_dtm, trans_code, account_num, xs3 order by trans_dtm) x ";

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows);


        $sql = "select trans_header_id, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm, trans_code, v.account_num, xs3,
                sum(xn2) as xn2, sum(amount1) as amount1
                from v_transaction v
                where trans_type_code = 'T004' and v.trans_status in ('APPROVAL', 'PAY_CONFIRM') ";

        if ($memberType == 4){
            $sql .= " and (v.xn3 is null or v.xn3 = 100) ";
        }else $sql .= " and v.xn3 = $memberId  ";

        if ($date1 != "" && $date2 != ""){
            $sql .= " and (STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d')) ";
        }

        if ($transCode != ""){
            $sql .= " and trans_code like '%$transCode%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " group by trans_header_id, trans_dtm, trans_code, account_num, xs3 order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataProduct = array();

        while($result->next()){
                $res = $result->current();

                if ($res['xs3'] == 1) $bayar = "Cash";
                else if ($res['xs3'] == 2) $bayar = "Tukar Point";
                else $bayar = "Transfer";

                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataProduct, array("Tgltrans" => $res['trans_dtm'],
                                             "KodeTrans" => $res['trans_code'],
                                             "NoAkun" => $res['account_num'],
                                             "JmlPembelian" => $res['xn2'],
                                             "Total" => $res['amount1'],
                                             "Pembayaran" => $bayar,
                                             "Aksi" => $aksi));

        }


        array_push($data, $dataProduct);

        return $data;
    }


    public function approveBuyProduct($transId, $flag){

        $dataInput = array($transId, $flag);

        $stmt = $this->_db->query("CALL Trx_BuyProductFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;

    }


    public function getAgentList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T002' and trans_status = 'APPROVAL' ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v
                where trans_type_code = 'T002' and trans_status = 'APPROVAL' ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'], "NoAkun" => $res['account_num'], "Nama" => $res['account_name'], "Alamat" => $res['xs5'], "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveAgent($transId, $memberId, $agentSN, $agentPIN, $flag){

        $dataInput = array($transId, $memberId, $agentSN, $agentPIN, $flag);


        $stmt = $this->_db->query("CALL Trx_AgentFollowup( ?, ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function claimReward($memberId, $accNum, $bonusRewardId, $rewardId){

        $dataInput = array($memberId, $accNum, $bonusRewardId, $rewardId);


        $stmt = $this->_db->query("CALL Trx_ClaimReward( ?, ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }

    public function getClaimRewardList($start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T007' and trans_status = 'APPROVAL' ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select *
                from v_transaction v, p_bonus_reward p
                where trans_type_code = 'T007' and trans_status = 'APPROVAL'
                and p.reward_id = v.xn1 ";


        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();
                $aksi = "<a href='javascript: void(0)' onclick='approve(".$res['trans_header_id'].")'>Approval</a>";

                array_push($dataBonus, array("Tgltrans" => $res['trans_dtm'],
                                            "NoAkun" => $res['account_num'],
                                            "Nama" => $res['account_name'],
                                            "Reward" => $res['reward_name'],
                                            "Aksi" => $aksi));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function approveReward($transId, $bonurRewardId, $flag){

        $dataInput = array($transId, $bonurRewardId, $flag);


        $stmt = $this->_db->query("CALL Trx_ClaimRewardFollowup( ?, ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function insertDataTrans($tableName, $dataTrans){
        $insert = $this->_sql->insert()
                ->into($tableName)
                ->values($dataTrans);

        $result = $this->execute($insert);

        return $result;
    }

    public function getRewardList($memberId, $numMitra, $start, $limit, $searchField = "") {

        $data = array(8);


        $sql = "select *
                from p_bonus_reward ";


        $sql = "select * from p_bonus_reward p
                left outer join t_bonus_reward t on p.reward_id = t.reward_id ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by p.reward_id ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataReward = array();
        while($result->next()){
                $res = $result->current();

                if ($res['member_id'] != ""){
                    $claim = "";
                    $sttsReward = "Milik Anda";

                    if ($res['status_withdrawal'] == "" || $res['status_withdrawal'] == 0)
                        $claim = "<a href='javascript: void(0)' onclick='claim(".$res['bonus_reward_id'].",".$res['reward_id'].")'>Cairkan</a>";
                    else if ($res['status_withdrawal'] == 1)
                        $claim = "Menunggu Approval";
                    else if ($res['status_withdrawal'] < 0)
                        $claim = "Klaim Dibatalkan";
                    else if ($res['status_withdrawal'] == 2)
                        $claim = "Sudah diklaim";


                    $sttsReward = "Milik Anda";
                }else{
                    $claim = "Cairkan";
                    $sttsReward = "Menunggu";
                }

                $sttsWD = ($res['status_withdrawal'] > 1) ? "V" : "X";

                array_push($dataReward, array("level" => $res['level'],
                                            "mitra" => $res['min_mitra'],
                                            "reward" => $res['reward_name'],
                                            "sttsreward" => $sttsReward,
                                            "klaim" => $claim,
                                            "sttswithdraw" => $sttsWD));
        }


        array_push($data, $dataReward);

        return $data;
    }

    public function getWalletTransaction($accNum, $transType, $date1, $date2, $start, $limit, $searchField = "", $wallet = "") {

        $sql = "select count(1) as total from (select trans_dtm
                ,sum(amount1) as amount1
                ,trans_type_name,  mutation_type,
                notes_txt,
                account_num, account_name
                from v_transaction
                where STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and account_num1 = '$accNum'  ";
        }

        if ($wallet == "E"){
            $sql .= " and ((trans_type_code in ('T008', 'T009', 'T010', 'T011') and mutation_type = 'K') or trans_type_code = 'T012') ";
        }else if  ($wallet == "REF"){
            $sql .= " and ((trans_type_code in ('T009') and mutation_type = 'D') or (trans_type_code = 'Q200' and amount2 = 2))";
        }else if  ($wallet == "ROI"){
            $sql .= " and ((trans_type_code = 'T008' and mutation_type = 'D') or trans_type_code = 'Q201')";
        }else if  ($wallet == "BIN"){
            $sql .= " and ((trans_type_code = 'T010' and mutation_type = 'D') or trans_type_code = 'Q202')";
        }else if  ($wallet == "SHOP"){
            $sql .= " and ((trans_type_code = 'T011' and mutation_type = 'D') or (trans_type_code = 'Q200' and amount2 = 4))";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }


        $sql .= " group by trans_header_id, trans_dtm ,
                trans_type_name, mutation_type, notes_txt, account_num, account_name) x";

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows, 1);

        $sql = "select
                trans_header_id, trans_code, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm, trans_type_id, trans_type_code, trans_type_name,
                account_num1, account_num2, amount1, amount2, mutation_type, notes_txt, trx_type,
                member_id, trans_status, account_num, account_name, external_param, external_resp,
                xn1, xn2, xs1, xs2, xs3, xs4, xs5, xs6, xn3, trans_detail_id, xd1, xd2, bank_acc_no,
                bank_acc_name, bank_city_name, bank_id, trans_type_desc
                from v_transaction
                where STR_TO_DATE(DATE_FORMAT(trans_dtm, '%Y%m%d'),'%Y%m%d') between STR_TO_DATE('$date1', '%Y%m%d') and STR_TO_DATE('$date2', '%Y%m%d') ";

        if ($accNum != ""){

            $sql .= " and  account_num1 = '$accNum' ";
        }

        if ($wallet == "E"){
            $sql .= " and ((trans_type_code in ('T008', 'T009', 'T010', 'T011') and mutation_type = 'K') or trans_type_code = 'T012') ";
        }else if  ($wallet == "REF"){
            $sql .= " and ((trans_type_code in ('T009') and mutation_type = 'D') or (trans_type_code = 'Q200' and amount2 = 2))";
        }else if  ($wallet == "ROI"){
            $sql .= " and ((trans_type_code = 'T008' and mutation_type = 'D') or trans_type_code = 'Q201')";
        }else if  ($wallet == "BIN"){
            $sql .= " and ((trans_type_code = 'T010' and mutation_type = 'D') or trans_type_code = 'Q202')";
        }else if  ($wallet == "SHOP"){
            $sql .= " and ((trans_type_code = 'T011' and mutation_type = 'D') or (trans_type_code = 'Q200' and amount2 = 4))";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                $kredit = "0";
                $debet = "0";

                if ($res['mutation_type'] == "K"){
                    $kredit = $res['amount1'];
                }else{
                    $debet = $res['amount1'];
                }

                if ($res['xn1'] == 1){  //Bank Wire

                    if ($res['xn3'] == 1) $paymentGW = "Bank Wire";
                    else if ($res['xn3'] == 2) $paymentGW = "Paypall";
                    else if ($res['xn3'] == 3) $paymentGW = "Perfect Money";
                    else $paymentGW = "Ego Pay";

                }else if ($res['xn1'] == 2) $paymentGW = "Perfect Money";
                else $paymentGW = "Ego Pay";

                $form = ($res['xn1'] == 1) ? 1 : 2;

                $accNum2 = ($res['account_num2'] == "") ? $res['notes_txt'] : $res['account_num2'];

                $detail = '<a data-toggle="modal" class="blue" role="button" href="#detail-form'.$form.'" onclick="detailTrans(\''.base64_encode(json_encode($res)).'\')">Details</a>';

                array_push($dataBonus, array("Tanggal" => $res['trans_dtm'],
                                             "JenisTransaksi" => $res['trans_type_name'],
                                             "NoAkun" => $res['account_num'],
                                             "NoAkun2" => $accNum2,
                                             "NamaAkun" => $res['account_name'],
                                             "Kredit" => $kredit,
                                             "Debet" => $debet,
                                             "paymentgw" => $paymentGW,
                                             "status" => $res['trans_status'],
                                             "detail" => $detail,
                                             "detail2" => $accNum2,
                                             "Type" => $res['trans_type_desc']
                                     ));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function getUpgradeMemberList($accNum, $start, $limit, $searchField = "") {

        $sql = "select count(1) as total, FORMAT(sum(amount1),2) as jumlah
                from v_transaction v
                where trans_type_code = 'T016' and v.trans_status = 'APPROVAL' ";

        if ($accNum != ""){
            $sql .= " and account_num like '%$accNum%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute();

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];
                $total = $res['jumlah'];
        }


        $data = array($rows, $total);


        $sql = "select 
                trans_header_id, trans_code, DATE_FORMAT(trans_dtm,'%d-%m-%Y %H:%i:%s') as trans_dtm, trans_type_id, trans_type_code, trans_type_name,
                account_num1, account_num2, amount1, amount2, mutation_type, notes_txt, trx_type,
                member_id, trans_status, account_num, account_name, external_param, external_resp,
                xn1, xn2, xs1, xs2, xs3, xs4, xs5, xs6, xn3, trans_detail_id, xd1, xd2, bank_acc_no,
                bank_acc_name, bank_city_name, v.bank_id,
                mt.*, b.*,
                getPaymentProof(v.account_num) as file_name
                from v_transaction v
                left outer join bank b on b.bank_id = v.bank_id,
                member_type mt
                where mt.member_type_id = v.xn2 and trans_type_code = 'T016'
                and v.trans_status = 'APPROVAL' ";

        if ($accNum != ""){
            $sql .= " and account_num like '%$accNum%' ";
        }

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by trans_dtm ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        $functModel = new \MLM\Model\Functions;

        $dataWD = array();
        while($result->next()){
                $res = $result->current();

                $currency = $functModel->getCurrencyName($res["xs1"]);
                $payment = $functModel->getPaymentName($res["xn3"]);

                $res["currency"] = $currency;
                $res["payment"] = $payment;

                $paymentProof = "";
                if ($res['file_name'] != ""){
                    $paymentProof = "<a data-toggle='modal' class='blue' role='button' href='#proof-form' onclick='detailProof(\"".$res['file_name']."\")'>".$res['file_name']."</a>";
                }


                $aksiPop = '<a data-toggle="modal" class="blue" role="button" href="#detail-form" onclick="detailTrans(\''.base64_encode(json_encode($res)).'\')">Approval</a>';

                array_push($dataWD, array("Tgltrans" => $res['trans_dtm'],
                                             "NoAkun" => $res['account_num'],
                                             "Plan" => $res['member_type_name'],
                                             "Amount" => $res['amount1'],
                                             "Currency" => $currency,
                                             "Payment" => $payment,
                                             "PaymentProof" => $paymentProof,
                                             "AksiPop" => $aksiPop
                                         ));
        }


        array_push($data, $dataWD);

        return $data;
    }


    public function approveUpgradeMember($transId, $flag){

        $dataInput = array($transId, $flag);

        $stmt = $this->_db->query("CALL Trx_UpgradeFollowup( ?, ?, @out_code, @out_msg)", $dataInput);
        $stmt = $this->_db->query("SELECT @out_code as out_code, @out_msg as out_msg");

        $data = $stmt->execute();

        return $data;
    }


    public function getREFCommissionTrans($memberId, $transType, $start, $limit, $searchField = "") {

        
        $sql = "SELECT count(1) as total
                FROM ( SELECT CPriorSponsor(member_id) AS member_id, @level AS level
                FROM ( SELECT @start_with := ? ,@id := @start_with,@level := 0 ) vars, members
                WHERE @id IS NOT NULL ) ho JOIN members hi ON hi.member_id = ho.member_id
                 ";

        
        
        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute(array($memberId));

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows, 1);

        
        $sql = "SELECT * FROM (
                SELECT DATE_FORMAT(start_dtm,'%d-%m-%Y %H:%i:%s') as  start_dtm, DATE_FORMAT(hi.start_dtm,'%d-%m-%Y') dat,
                DATE_FORMAT(hi.start_dtm,'%H:%i:%s') tim, hi.member_code,
                hi.subs_amount,parent_id, level , 0.15 * hi.subs_amount comm, hi.sponsor_id
                FROM ( SELECT CPriorSponsor(member_id) AS member_id, @level AS level
                FROM ( SELECT @start_with := ? ,@id := @start_with,@level := 0 ) vars, members
                WHERE @id IS NOT NULL ) ho JOIN members hi ON hi.member_id = ho.member_id) xx
                ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }
        
        $sql .= " order by start_dtm desc ";
        
        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($memberId));

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                array_push($dataBonus, array("date" => $res['dat'],
                                             "time" => $res['tim'],
                                             "NoAkun" => $res['member_code'],
                                             "payment" => $res['subs_amount'],
                                             "level" => $res['level'],
                                             "commission" => $res['comm']
                                     ));
        }


        array_push($data, $dataBonus);

        return $data;
    }


    public function getBINCommissionTrans($memberId, $transType, $start, $limit, $searchField = "") {

        $sql = "SELECT count(1) as total
                FROM t_bonus_pasang WHERE member_id = ? ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $stmt = $this->_db->query($sql);
        $getTotal = $stmt->execute(array($memberId));

        while($getTotal->next()){
                $res = $getTotal->current();
                $rows = $res['total'];

        }


        $data = array($rows, 1);

        $sql = "SELECT *
                FROM t_bonus_pasang WHERE member_id = ? ";

        if ($searchField != ""){
            $sql .= " and $searchField ";
        }

        $sql .= " order by period desc ";

        if ($limit != ""){
            $sql .= " limit $start, $limit";
        }

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute(array($memberId));

        $dataBonus = array();
        while($result->next()){
                $res = $result->current();

                $total =  $res['total_bonus'] * 400 * 0.1;

                array_push($dataBonus, array("date" => $res['period'],
                                             "left" => $res['amt_ki'],
                                             "right" => $res['amt_ka'],
                                             "pair" => $res['total_bonus'],
                                             "carryleft" => $res['m_ki'],
                                             "carryright" => $res['m_ka'],
                                             "total" => $total
                                     ));
        }


        array_push($data, $dataBonus);

        return $data;
    }

}
